package com.automobile.dao;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.mapping.FetchType;
import org.springframework.stereotype.Repository;

import com.automobile.domain.Announce;
import com.automobile.domain.BeanPage;

@Repository("announceDao")
public interface AnnounceDao {
	
	@Select("select * from announce where userid = #{userId}")
	@Results(id = "announceAll",value = {
			@Result(property = "id",column = "id",id = true),
			@Result(property = "title",column = "title"),
			@Result(property = "message",column = "message"),
			@Result(property = "date",column = "date"),
			@Result(property = "userId",column = "userId"),
			@Result(property = "status",column = "status"),
			@Result(property = "carBrandId" ,column = "carBrandId"),
			@Result(property = "carTypeId" ,column = "carTypeId"),
			@Result(property = "money" ,column = "money"),
			@Result(property = "age",column = "age"),
			@Result(property = "km",column = "km"),
			@Result(property = "standard",column = "standard"),
			@Result(property = "displacement",column = "displacement"),
			@Result(property = "gearbox",column = "gearbox"),
			@Result(property = "country",column = "country"),
			@Result(property = "colour",column = "colour"),
			@Result(property = "capabilities" ,column = "capabilities"),
            @Result(property = "carBrand",column = "carBrandId",one = @One(select = "com.automobile.dao.CarBrandDao.findBrandNameById",fetchType = FetchType.LAZY)),
            @Result(property = "carType",column = "carTypeId",one = @One(select = "com.automobile.dao.CarTypeDao.findTypeNameById",fetchType = FetchType.LAZY)),
			@Result(property = "imgs",column = "id",many = @Many(select = "com.automobile.dao.ImgDao.findImgs",fetchType= FetchType.LAZY)),
            @Result(property = "user",column = "userId",one = @One(select = "com.automobile.dao.UserDao.findById",fetchType = FetchType.LAZY))
	})
	List<Announce> findAnnounceByUserId(int userId);//查看用户发过的帖或者商品
	
	/*
	 * 查找
	 * 		标题
	 * 		类型
	 * 		当前第几页
	 * 		页数大小
	 * 		是否是商品 		1/0
	 */
	@Select({" <script> "
			+ " select * from announce where id != 0 and status = \"Y\" "
			+ " <if test = 'title != null'> and title like #{title} </if> "
			+ " <if test = 'carBrand != null'> and carBrandId = (select id from brand where name = #{carBrand}) </if> "
			+ " <if test = 'carType != null'> and carTypeId = (select id from type where name = #{carType}) </if> "
			+ " <if test = 'money != null'> "
				+ "<if test='money == 1'>and money between 0 and 30000 </if>"
				+ "<if test='money == 2'>and money between 30001 and 50000 </if>"
				+ "<if test='money == 3'>and money between 50001 and 80000 </if>"
				+ "<if test='money == 4'>and money between 80001 and 100000 </if>"
				+ "<if test='money == 5'>and money between 100000 and 150000 </if>"
				+ "<if test='money == 6'>and money between 150000 and 200000 </if>"
				+ "<if test='money == 7'>and money between 200000 and 300000 </if>"
				+ "<if test='money == 8'>and money > 300000  </if>"
			+ " </if> "
			+ " <if test = 'age != null'> and age = #{age} </if> "
			+ " <if test = 'km != null'> and km = #{km} </if> "
			+ " <if test = 'standard != null'> and standard = #{standard} </if> "
			+ " <if test = 'displacement != null'> and displacement = #{displacement} </if> "
			+ " <if test = 'gearbox != null'> and gearbox = #{gearbox} </if> "
			+ " <if test = 'country != null'> and country = #{country} </if> "
			+ " <if test = 'colour != null'> and colour = #{colour} </if> "
			+ " <if test = 'capabilities != null'> and capabilities = #{capabilities} </if> "
            + " ORDER BY id DESC "
			+ " limit #{pageCount},#{pageSize}"
			+ " </script>"})
	@ResultMap("announceAll")
	List<Announce> findAnnounce(BeanPage beanPage);
	
	@Select("select * from announce where id = #{announceId}")
	@ResultMap("announceAll")
	Announce findAnnounceById(int announceId);//详情查询一个
	
	@Select("select * from announce where id != 0")
	@ResultMap("announceAll")
	List<Announce> findAnnounceAll();
	
	@Select({" <script> "
			+ " select count(*) from announce where 1 = 1 "
			+ " <if test = 'title != null'> and title like #{title} </if> "
			+ " <if test = 'carBrand != null'> and carBrandId = (select id from brand where name = #{carBrand}) </if> "
			+ " <if test = 'carType != null'> and carTypeId = (select id from type where name = #{carType}) </if> "
			+ " <if test = 'money != null'> "
				+ "<if test='money == 1'>and money between 0 and 30000 </if>"
				+ "<if test='money == 2'>and money between 30001 and 50000 </if>"
				+ "<if test='money == 3'>and money between 50001 and 80000 </if>"
				+ "<if test='money == 4'>and money between 80001 and 100000 </if>"
				+ "<if test='money == 5'>and money between 100000 and 150000 </if>"
				+ "<if test='money == 6'>and money between 150000 and 200000 </if>"
				+ "<if test='money == 7'>and money between 200000 and 300000 </if>"
				+ "<if test='money == 8'>and money > 300000  </if>"
			+ " </if> "
			+ " <if test = 'age != null'> and age = #{age} </if> "
			+ " <if test = 'km != null'> and km = #{km} </if> "
			+ " <if test = 'standard != null'> and standard = #{standard} </if> "
			+ " <if test = 'displacement != null'> and displacement = #{displacement} </if> "
			+ " <if test = 'gearbox != null'> and gearbox = #{gearbox} </if> "
			+ " <if test = 'country != null'> and country = #{country} </if> "
			+ " <if test = 'colour != null'> and colour = #{colour} </if> "
			+ " <if test = 'capabilities != null'> and capabilities = #{capabilities} </if> "
			+ " </script>"})
	int findAnnounceCountNum(BeanPage beanPage);

    @Insert("<script> insert into announce(title,message,carBrandId,carTypeId,userid,money,age,km,standard,displacement,gearbox,country,colour,capabilities <if test = 'status!=null'>,status</if> ) values(#{title},#{message},#{carBrandId},#{carTypeId},#{userId},#{money},#{age},#{km},#{standard},#{displacement},#{gearbox},#{country},#{colour},#{capabilities} <if test = 'status!=null'>,#{status}</if> ) </script>")
    @Options(useGeneratedKeys=true, keyProperty="id", keyColumn="id")
	int save(Announce announce);//保存
	
	@Update("update announce set title = #{title} , message = #{message} , carBrandId = #{carBrandId} , carTypeId = #{carTypeId} ,money = #{money} , age = #{age},km =#{km},standard=#{standard},displacement=#{displacement},gearbox=#{gearbox},country=#{country},colour=#{colour},capabilities =#{capabilities} where id = #{id}")
    Announce update(Announce announce);//更新
	
	@Delete("delete from announce where id = #{id}")
	void delete(int id);//删除
	
}
